![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
I/O Configuration The function of a parallel query is to split up query operations so that they more effectively take advantage of the system. One of the ways a parallel query does this is by allowing the processing of the query to continue while pieces of the query operation are stalled waiting for I/Os to complete. Parallel queries are not effective if the entire table is limited to one disk drive. By striping the table across many drives, I/Os can be distributed and a higher level of parallelism can occur. Striping can be done with OS striping, with Oracle striping, or (better yet) with a hardware disk array. Refer to Chapters 14 and 15 for detailed information on this topic. Large contiguous extents can also help performance in parallel query operations. During scan operations, the query coordinator splits contiguous ranges of blocks into large, medium, and small groups of blocks. Each query server is given a large group of blocks to start with, progressively working its way down to the small group of blocks until the scan is completed. This is done to try to balance the load done by each query server. If there are several large extents in a table, the query coordinator can find blocks to dispatch to the query servers much more easily.
Degree of Parallelism Properly distributing I/Os and the degree of parallelism are the two most important things to tune in the Parallel Query option. Tuning the degree of parallelism is partially trial and error and partially analysis. It is very important to take notes when you are experimenting with the degree of parallelism. Your first guess should be based on the following factors:
All these parameters should have some influence on the degree of parallelism you set up for your system. Remember that the preceding points are just guidelines to help with your best guess as a starting point. Here are a few other suggestions:
Once you determine your starting point, you can monitor your system by querying the dynamic performance table, V$PQ_SYSSTAT. You can do this with the query shown here: SQL> select * from v$pq_sysstat; STATISTIC VALUE ------------------------------ ---------- Servers Busy 0 Servers Idle 12 Servers Highwater 16 Server Sessions 380 Servers Started 4 Servers Shutdown 4 Servers Cleaned Up 0 Queries Initiated 21 DFO Trees 77 Local Msgs Sent 2459361 Distr Msgs Sent 0 Local Msgs Recv'd 2459318 Distr Msgs Recv'd 0 13 rows selected. When looking at the output from this query, you will find the following statistics quite useful:
Once you determine your degree of parallelism, begin testing; evaluate the information you get from V$PQ_SYSSTAT and from your operating system-monitoring facilities. Keep an eye out for CPU usage and excessive waiting for I/O. If the CPU usage is too high, try reducing the degree of parallelism. If the CPU usage is too low and there is significant waiting for I/O, try increasing the degree of parallelism. Remember that the degree of parallelism is determined by SQL hints, table definitions, and initialization parameters. The total number of query servers is determined by the initialization parameter, PARALLEL_MAX_SERVERS; the number started up initially is determined by the initialization parameter, PARALLEL_MIN_SERVERS. The total number of query servers in use is the number of queries executed in parallel multiplied by their degree of parallelism. If you try to use more than PARALLEL_MAX_SERVERS, you will not be able to parallelize your query.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |